*** Country-level data ***
clear
set more off
global directory = "INSERT YOUR DIRECTORY"

* 1. Read data from combined IMF/OECD excel sheet and generate variables

* real effective exchange rate
import excel using $directory\Daten\macro_combined_6ctrs_b.xlsx, cellrange(C3:AK8) clear
destring C-AK , replace
xpose, clear
rename v1 reer_deu
rename v2 reer_gre
rename v3 reer_irl
rename v4 reer_ita
rename v5 reer_por
rename v6 reer_esp
gen year = _n + 1979

foreach str in deu gre irl ita por esp {		/* currently normalized to 2010. Normalize to 1995 instead. */
	gen `str'1995 = reer_`str'[16]
	replace reer_`str' = (reer_`str' / `str'1995) * 100
	drop `str'1995
	}
save $directory\Daten\reer.dta, replace

* short term interest rates
import excel using $directory\Daten\macro_combined_6ctrs_b.xlsx, cellrange(C11:AK16) clear
destring C-AK , replace
xpose, clear
rename v1 stint_deu
rename v2 stint_gre
rename v3 stint_irl
rename v4 stint_ita
rename v5 stint_por
rename v6 stint_esp
gen year = _n + 1979

save $directory\Daten\stint.dta, replace


* inflation rates
import excel using $directory\Daten\macro_combined_6ctrs_b.xlsx, cellrange(C19:AK24) clear
destring C-AK , replace
xpose, clear
rename v1 inflat_deu
rename v2 inflat_gre
rename v3 inflat_irl
rename v4 inflat_ita
rename v5 inflat_por
rename v6 inflat_esp
gen year = _n + 1979

save $directory\Daten\inflat.dta, replace


* GDP
import excel using $directory\Daten\macro_combined_6ctrs_b.xlsx, cellrange(C35:AK40) clear
destring C-AK , replace
xpose, clear
rename v1 gdp_deu
rename v2 gdp_gre
rename v3 gdp_irl
rename v4 gdp_ita
rename v5 gdp_por
rename v6 gdp_esp
gen year = _n + 1979

foreach var of varlist gdp_deu-gdp_esp {
	replace `var' = `var' / 1000		/* express in billion USD */
	}
save $directory\Daten\gdp.dta, replace


* value added, employment, investment: manufacturing and construction
import excel using $directory\Daten\macro_combined_6ctrs_b.xlsx, cellrange(I44:AF61) clear
destring I-AF , replace
xpose, clear
rename v1 valadd_manuf_deu
rename v2 valadd_manuf_irl
rename v3 valadd_manuf_esp
rename v4 valadd_constr_deu
rename v5 valadd_constr_irl
rename v6 valadd_constr_esp
rename v7 empl_manuf_deu
rename v8 empl_manuf_irl
rename v9 empl_manuf_esp
rename v10 empl_constr_deu
rename v11 empl_constr_irl
rename v12 empl_constr_esp
rename v13 inve_manuf_deu
rename v14 inve_manuf_irl
rename v15 inve_manuf_esp
rename v16 inve_constr_deu
rename v17 inve_constr_irl
rename v18 inve_constr_esp
gen year = _n + 1985
 
save $directory\Daten\stan.dta, replace


*** combine datasets
merge 1:1 year using $directory\Daten\reer.dta, nogen
merge 1:1 year using $directory\Daten\stint.dta, nogen
merge 1:1 year using $directory\Daten\inflat.dta, nogen
merge 1:1 year using $directory\Daten\gdp.dta, nogen

* generate meaningful variables
foreach str in deu gre irl ita por esp {
	gen rint_`str' = stint_`str' - inflat_`str'	/* real short term interest rate */
	drop stint_`str' inflat_`str'
	drop gdp_`str'
	}
	
order year reer_* rint_*
save $directory\Daten\macro_indicators_6ctr.dta, replace
erase $directory\Daten\stan.dta
erase $directory\Daten\reer.dta
erase $directory\Daten\stint.dta
erase $directory\Daten\inflat.dta
erase $directory\Daten\gdp.dta


* generate variables for use with LIAB 
gen reer_iips = (reer_irl + reer_ita + reer_por + reer_esp) / 4
gen reer_deuiips = reer_deu - reer_iips
gen reer_is = (reer_irl + reer_esp) / 2
gen reer_deuis = reer_deu - reer_is

gen rint_iips = (rint_irl + rint_esp + rint_ita + rint_por) / 4
gen rint_deuiips = rint_deu - rint_iips
gen rint_is = (rint_irl + rint_esp) / 2
gen rint_deuis = rint_deu - rint_is

keep year reer_deu reer_esp reer_irl reer_iips reer_deuiips reer_is reer_deuis rint_deu rint_iips rint_deuiips rint_is rint_deuis
keep if year >= 1985

label variable reer_deu "real effective exch. rate DEU"
label variable reer_iips "avg. real effective exch. rate IIPS"
label variable reer_deuiips "diff. real eff. exch rate DEU-IIPS"
label variable reer_is "avg. real effective exch. rate Ireland, Spain"
label variable reer_deuis "diff. real eff. exch rate DEU-IS"
label variable rint_deu "real interest rate DEU"
label variable rint_iips "avg. real interest rate IIPS"
label variable rint_deuiips "diff. real interest rate DEU-IIPS" 
label variable rint_is "avg. real interest rate Ireland, Spain"
label variable rint_deuis "diff. real interest rate DEU-IS" 

order year reer_* rint_*
saveold $directory\Output\macrovars_deuiips_85-14_b.dta, replace


*** create counterfactual values for post-1995/1998 ***

foreach var of varlist rint_deuiips rint_deuis {
	gen `var'_cf1 = (`var'[1] + `var'[2] + `var'[3] + `var'[4] + `var'[5] + `var'[6] + `var'[7]) / 7		/* counterfactual is avg value 1985-1991 */
	replace `var'_cf1 = `var' if year <= 1991
	}

foreach var of varlist rint_deuiips rint_deuis {
	gen `var'_cf2 = (`var'[1] + `var'[2] + `var'[3] + `var'[4] + `var'[5] + `var'[6] + `var'[7] + `var'[8] + `var'[9] + `var'[10]) / 10		/* counterfactual is avg value 1985-1995 */
	replace `var'_cf2 = `var' if year <= 1995
	}
	
foreach var of varlist reer_deuiips reer_deuis {
	gen `var'_cf1 = `var'[14]		/* counterfactual is 1998 value. exchange rates could adjust prior to 1999 */
	replace `var'_cf1 = `var' if year <= 1998
	gen `var'_cf2 = `var'
	
	foreach year of numlist 1999/2008 {
		local val8595 = (`var'[1] + `var'[2] + `var'[3] + `var'[4] + `var'[5] + `var'[6] + `var'[7] + `var'[8] + `var'[9] + `var'[10]) / 10	 /* avg. value 1985 - 1995 */
		local dist    = 2015 - 1998 		/* years to go between 1998 and 2015 */
		local passed  = `year' - 1998		/* years past since 1998  */
		local fract	  = `passed' / `dist'	/* proportion of years passed of the period between 1999 and 2015 */
		replace `var'_cf2 = `var'[14] * (1 - `fract') + `val8595' * (`fract') if year==`year' /* counterfactual is a linear reversion to pre 1995 level until 2015 starting after 1998. high in 90s was artificial */ 
		}
	}

* rint deu-is * Difference in real interest rates: Germany - Ireland/Spain", size(medsmall) margin(medlarge)) *
gen y1 =-3
gen y2 =-1
gen x =1990

twoway 	(line rint_deuis year if year <= 2012, lcolor(black*.7) lpattern(solid) lwidth(medium)) /// 
		(lowess rint_deuis year if year <=2012, bwidth(.3) lcolor(black*.7) lpattern(shortdash) lwidth(medthick)) /// 
		(line rint_deuis_cf2 year if year > 1994 & year <=2008, lcolor(black*.7) lpattern(longdash) lwidth(medthick)), ///
		xtick(1985[1]2012, grid glcolor(white) tlcolor(black) tlwidth(thin)) ///
		xline(1992 1995 1998.5 2006 2008, lcolor(black*.1) lwidth(thick)) /// 
		xmlabel(1985[2]2011, labsize(small)) ///
		xlabel(1992 "Maastricht" 1995 "Madrid Summit" 1998.5 "Euro" 2006 "mortgage boom peaks" 2008 "Lehman", notick angle(30) labsize(medsmall) labgap(6)) ///
		ylabel(-6[2]5, nogrid angle(horizontal) tlcolor(black) tlwidth(medium) tlength(1) labsize(medsmall) labcolor(black)) ///
		ytick(-6[.5]5, nogrid tlcolor(black) tlwidth(thin)) ///
		ytick(0, grid glcolor(black*.1) glwidth(thinmed) tlcolor(black) tlwidth(thin)) ///
		plotregion(color(white)) graphregion(color(white)) ///
		title("") ///
		yscale(lcolor(black) titlegap(5)) /// 
		xscale(lcolor(black) titlegap(2)) xtitle("") /// 	
		legend(rows(1) label(1 "observed trend") label(2 "lowess") label(3 "counterfactual trend") region(lcolor(white)))		
	graph save Graph $directory\Output\rint_deuis.gph, replace

* reer for 3 countries *
twoway 	(line reer_deu year	if year<=2012 & year>=1990, lcolor(black*.7) 		lpattern(solid) lwidth(medthick)) /// 
		(line reer_irl year	if year<=2012 & year>=1990, lcolor(black*.7) 	lpattern(shortdash) lwidth(medthick)) ///
		(line reer_esp year	if year<=2012 & year>=1990, lcolor(black*.7) 	lpattern(longdash) lwidth(medthick)), ///
		xlabel(1985 1995 1999 2007, tlcolor(black) tlwidth(medium) tlength(1)) ///
		xtick(1990[1]2012, grid glcolor(white) tlcolor(black) tlwidth(thin)) ///
		xline(1992 1995 1998.5 2006 2008, lcolor(black*.1) lwidth(thick))  /// 
		xmlabel(1991[2]2012, labsize(small)) ///
		xlabel(1992 "Maastricht" 1995 "Madrid Summit" 1998.5 "Euro" 2006.5 "mortgage boom peak" 2008 "Lehman", notick angle(30) labsize(medsmall) labgap(6)) ///
		ylabel(80[10]120, nogrid angle(horizontal)) ///
		plotregion(color(white)) graphregion(color(white)) ///
		title("") ///
		yscale(lcolor(black) titlegap(5)) /// 
		xscale(lcolor(black) titlegap(2)) xtitle("")	/// 	
		legend(rows(1) label(1 "Germany") label(2 "Ireland") label(3 "Spain") region(lcolor(white)))		
	graph save Graph $directory\Output\reer_3ctr.gph, replace
	
* current account balance *	
import excel using $directory\Daten\oecd_curaccountbal_3ctrs.xlsx, cellrange(F2:AG4) clear
destring F-AG , replace
xpose, clear
rename v1 cabygdp_deu
rename v2 cabygdp_irl
rename v3 cabygdp_esp
gen year = _n + 1985

twoway 	(line cabygdp_deu year if year>=1990 & year<=2013, lpattern(solid) lcolor(black*.7) lwidth(medthick))  ///
		(line cabygdp_irl year if year>=1990 & year<=2013, lpattern(shortdash) lcolor(black*.7) lwidth(medthick))  ///
		(line cabygdp_esp year if year>=1990 & year<=2013, lpattern(longdash) lcolor(black*.7) lwidth(medthick)), ///
/*titles*/		title("") ytitle("") xtitle("") ///
/*legend*/		legend(label(1 "Germany") 	label(2 "Ireland") label(3 "Spain") rows(1) region(lcolor(white)))	///	
/*gen. look*/	plotregion(color(white))	graphregion(color(white)) ///
				yscale(lcolor(black) titlegap(5))	xscale(lcolor(black) titlegap(2)) ///
				ylabel(0, angle(horizontal) notick grid glcolor(black*.1) glwidth(medthick) labsize(medsmall)) ///
				ymlabel(-10 -5 5 10, nogrid angle(horizontal) notick labsize(medsmall)) ///
/*axis labels*/	xmlabel(1991[2]2013, labsize(small)) ///
				xlabel(1992 "Maastricht" 1995 "Madrid Summit" 1998.5 "Euro" 2006.5 "mortgage boom peak" 2008 "Lehman", notick angle(30) labsize(medsmall) labgap(6)) ///
/*grid&ticks*/	xtick(1990[1]2013, grid glcolor(white) tlcolor(black) tlwidth(thin)) /// 
				xline(1992 1995 1998.5 2006 2008, lcolor(black*.1) lwidth(thick))
				graph save Graph $directory\Output\cabalance.gph, replace

	
* Value added as prop GDP - construction * Value added by construction industry, 1986-2009", size(medsmall) margin(medlarge)) 
use $directory\Daten\macro_indicators_6ctr.dta, clear

twoway	(line valadd_constr_deu year if year>=1986 & year <=2009, lpattern(solid) 	lcolor(black*.55) lwidth(thick)) ///
		(line valadd_constr_irl year if year>=1986 & year <=2009, lpattern(shortdash) 	lcolor(black*.6) lwidth(thick))  ///
		(line valadd_constr_esp year if year>=1986 & year <=2009, lpattern(longdash) lcolor(black*.6) lwidth(thick)), ///
/*legend*/		legend(off) xtitle("") ytitle("Construction", size(large))	///	
/*gen. look*/	plotregion(color(white))	graphregion(color(white)) ///
				yscale(lcolor(black) titlegap(5))	xscale(lcolor(white) titlegap(2)) ///
/*grid&ticks*/	xtick(1986[1]2009, nogrid notick) /// 
				xlabel(1995 "Madrid Summit" 2006 "mortgage boom peak", notick angle(0) labsize(medsmall) labgap(0)) ///
				xmlabel(1992 "Maastricht" 1998.5 "Euro" 2008 "Lehman", notick angle(0) labsize(medsmall) labgap(3.5)) 	///
				ytick(4[2]12, nogrid tlcolor(black) tlwidth(thin)) ///
				ylabel(4[2]12, nogrid angle(horizontal)) ///
				xline(1992 1995 1998.5 2006 2008, lcolor(black*.1) lwidth(thick)) ///
				fysize(320) fxsize(100)	saving(construction)
				
* Value added as prop GDP - manufacturing
twoway	(line valadd_manuf_deu year if year>=1986 & year <=2009, lpattern(solid) lcolor(black*.55)  lwidth(thick)) ///
		(line valadd_manuf_irl year if year>=1986 & year <=2009, lpattern(shortdash)  lcolor(black*.6) lwidth(thick))  ///
		(line valadd_manuf_esp year if year>=1986 & year <=2009, lpattern(longdash) lcolor(black*.6) lwidth(thick)), ///
/*legend*/		legend(label(1 "Germany") 	label(2 "Ireland") label(3 "Spain") size(large) rows(1) region(lcolor(white)))	///
				xtitle("") ytitle("Manufacturing", size(large))	///	
/*gen. look*/	plotregion(color(white))	graphregion(color(white)) ///
				yscale(lcolor(black) titlegap(5))	xscale(lcolor(black) titlegap(2)) ///
/*axis labels*/	xmlabel(1987[2]2009, labsize(medsmall)) ///
				xlabel(none) tlabel(none) ///
/*grid&ticks*/	xtick(1986[1]2009, nogrid tlcolor(black) tlwidth(thin)) /// 
				ytick(10[5]35, nogrid tlcolor(black) tlwidth(thin)) ///
				ylabel(10[5]35, nogrid angle(horizontal)) ///
				xline(1992 1995 1998.5 2006 2008, lcolor(black*.1) lwidth(thick)) ///
				fysize(370) fxsize(100)	saving(manufacturing)
				
graph combine construction.gph manufacturing.gph, rows(2) imargin(0 0 0 0) graphregion(color(white))
	graph save Graph $directory\Output\3ctr_valadd_combined.gph, replace
	erase construction.gph
	erase manufacturing.gph
			
				
*** Germany: houseprices and construction orders from Federal Statististical Office

* 1. Read construction orders into stata (1992-2015)
import excel using $directory\Daten\destatis_orders.xlsx, cellrange(C116:C210) clear
gen qtr_count = _n 
gen yr = (qtr_count - 1) / 4
recast int yr, force
gen year = 1992 + yr
bysort year: gen qtr = _n
gen quarter = yq(year, qtr)
format quarter %tq
rename C orders
drop yr qtr
save $directory\Daten\constr_ordervalue.dta , replace

* 2. Read houseprices into stata
import excel using $directory\Daten\destatis_houseprices.xlsx , cellrange(C7:BM7) clear
xpose, clear
rename v1 houseprices
gen qtr_count = _n + (8*4)
save $directory\Daten\constr_houseprices.dta, replace

use $directory\Daten\constr_ordervalue.dta, clear
merge 1:1 qtr_count using $directory\Daten\constr_houseprices.dta , nogen
drop if year>2015
tsset quarter

tsline orders if year < 2016, saving(orders) lpattern(dash) lcolor(black*.7) lwidth(thick) ///
	ttitle("") plotregion(color(white)) graphregion(color(white)) ///
	ytitle("Orders", size(vlarge) margin(medlarge)) ///
	yscale(lcolor(black)) tscale(lcolor(white) titlegap(2)) tlabel(none) ///
	ttick(1992q1(4)2015q1, grid glcolor(white) tlcolor(black) tlwidth(medium) tlength(0)) /// 
	ylabel(80[10]150, angle(horizontal) labsize(medium)) ///
	ytick(80[10]150, grid glcolor(white) tlcolor(black) tlwidth(thin)) ///
	tline(1992q2 1995q4 1998q4 2007q1 2008q3, extend lcolor(black*.1) lwidth(vthick)) ///
	tlabel(1992q2 "Maastricht" 1995q4 "Madrid" 2007q1 "mortgage boom peak", notick labgap(0) labsize(medium)) ///
	tmlabel(1998q4 "Euro" 2008q3 "Lehman", notick labgap(3.5) labsize(medium)) ///
	
tsline houseprices if year < 2016, saving(houseprices) lpattern(solid) lcolor(black*.7) lwidth(thick) ///
	ttitle("") plotregion(color(white)) graphregion(color(white)) ///
	ytitle("House prices", size(vlarge) margin(medlarge)) ///
	yscale(lcolor(black)) tscale(lcolor(black) titlegap(2)) ///
	ttick(1992q1(4)2015q1, nogrid tlcolor(black) tlwidth(thin)) /// 
	ylabel(100[10]130, angle(horizontal) labsize(medium)) tlabel(1993q1(8)2015q1, angle(45) labsize(medium)) ///
	ytick(100[10]130, grid glcolor(white) tlcolor(black) tlwidth(thin)) ///
	tline(1992q2 1995q4 1998q4 2007q1 2008q3, extend lcolor(black*.1) lwidth(vthick))
	
graph combine orders.gph houseprices.gph, rows(2) imargin(0 0 0 0) graphregion(color(white)) title("", size(medlarge) margin(medlarge))
	graph save Graph $directory\Output\deu_house.gph , replace
	erase orders.gph
	erase houseprices.gph
				
clear
erase $directory\Daten\constr_houseprices.dta
erase $directory\Daten\constr_ordervalue.dta
				

				
				
*** Volume of trade (imports, exports) of Germany, Denmark, Sweden, Switzerland, UK with Eastern Europe and China

*** trade with Eastern Europe and China

* 1. Read data into stata
import delimited $directory\Daten\untrade_east_part1.csv , clear
save $directory\Daten\untrade_east_part1.dta, replace

import delimited $directory\Daten\untrade_east_part2.csv , clear
save $directory\Daten\untrade_east_part2.dta, replace

import delimited $directory\Daten\untrade_china.csv , clear
save $directory\Daten\untrade_china.dta, replace

import delimited $directory\Daten\untrade_cz_1992.csv , clear		/* the following is necessary bec Czech Rep. an Slowakia were still Czechoslowakia in 1992 */
replace tradevalueus = tradevalueus / 2 										/* divide trade volumes by two and replace code with Czech Rep. */
replace partnercode = 203
save $directory\Daten\untrade_cz_1992.dta , replace	

import delimited $directory\Daten\untrade_cz_1992.csv , clear		/* the following is necessary bec Czech Rep. an Slowakia were still Czechoslowakia in 1992 */
replace tradevalueus = tradevalueus / 2 										/* divide trade volumes by two and replace code with Czech Rep. */
replace partnercode = 203
save $directory\Daten\untrade_cz_1992.dta, replace	

import delimited C:\Users\fochsenfeld\Documents\Forschung_home\data\UN\untrade_cz_1992.csv, clear		/* the following is necessary bec Czech Rep. an Slowakia were still Czechoslowakia in 1992 */
replace tradevalueus = tradevalueus / 2 										/* divide trade volumes by two and replace code with Czech Rep. */
replace partnercode = 703

append using $directory\Daten\untrade_cz_1992.dta
append using $directory\Daten\untrade_east_part1.dta
append using $directory\Daten\untrade_east_part2.dta
append using $directory\Daten\untrade_china.dta

label define tradeflowcode 1 "Import" 2 "Export"
label value tradeflowcode tradeflowcode 
label define reportercode 208 "Denmark" 276 "Germany" 752 "Sweden" 757 "Switzerland"   
label value reportercode reportercode
drop if reportercode == 826 	/* UK does not have manufacturing structure similar to GER, DK, SWE, CH */  
label define partnercode 156 "China" 100 "Bulgaria" 348 "Hungary" 642 "Romania" 703 "Slovakia" 705 "Slovenia" 203 "Czech Rep." 233 "Estonia" 428 "Latvia" 440 "Lithuania" 616 "Poland" 
label value partnercode partnercode
gen tradevalueusbn = tradevalueus / (10^9)  /* in billion us-dollars */
keep year tradeflowcode reportercode partnercode tradevalueusbn
sort reportercode partnercode year tradeflowcode
keep if year >= 1992
save  $directory\Daten\untrade.dta , replace
erase $directory\Daten\untrade_cz_1992.dta
erase $directory\Daten\untrade_east_part1.dta
erase $directory\Daten\untrade_east_part2.dta
erase $directory\Daten\untrade_china.dta

* import usd-ntlcurrency exchange rate
import excel using  $directory\Daten\oecd_exchangerates_various.xlsx , cellrange(D7:Z10) clear
xpose, clear
gen year = 1991 + _n 
rename v1 dkperusd
rename v2 gerperusd
rename v3 sweperusd
rename v4 chperusd
merge 1:m year using $directory\Daten\untrade.dta
drop _merge

* convert trade volume into national currencies
gen tradevalue = .
replace tradevalue = tradevalueusbn * dkperusd  if reportercode == 208
replace tradevalue = tradevalueusbn * gerperusd if reportercode == 276
replace tradevalue = tradevalueusbn * sweperusd if reportercode == 752
replace tradevalue = tradevalueusbn * chperusd  if reportercode == 757
drop tradevalueusbn dkperusd gerperusd sweperusd chperusd
save $directory\Daten\untrade.dta , replace

* convert into 2010 national currency, using consumer price indices
* import consumer price indices
import excel using $directory\Daten\IMF_cpis.xlsx , cellrange(B3:X6) clear
xpose, clear
gen year = 1991 + _n
rename v1 dkcpi
rename v2 gercpi
rename v3 swecpi
rename v4 chcpi
merge 1:m year using $directory\Daten\untrade.dta 
drop if year == 2013 | year == 2014

gen tradevalue_real = .
replace tradevalue_real = tradevalue *  (dkcpi/100) if reportercode == 208
replace tradevalue_real = tradevalue * (gercpi/100) if reportercode == 276
replace tradevalue_real = tradevalue * (swecpi/100) if reportercode == 752
replace tradevalue_real = tradevalue *  (chcpi/100) if reportercode == 757
drop _merge tradevalue dkcpi gercpi swecpi chcpi
sort reportercode year tradeflowcode partnercode
save $directory\Daten\untrade.dta , replace

* generate total trade volume
keep if tradeflowcode == 1
rename tradevalue_real imports
save $directory\Daten\untrade_temp_imp.dta , replace

use $directory\Daten\untrade.dta, clear
keep if tradeflowcode == 2
rename tradevalue_real exports
merge 1:1 year reportercode partnercode using $directory\Daten\untrade_temp_imp.dta
drop _merge tradeflowcode
sort reportercode partnercode year

* Danish 1997 exports are missing: impute linear trend between 1996 and 1998.
bysort reportercode partnercode: replace exports = (exports[_n-1] + exports[_n+1]) / 2 if exports[_n] == . 

sort reportercode year partnercode
bysort reportercode year: gen exports_total = exports[1] + exports[2] + exports[3] + exports[4] + exports[5] + exports[6] + exports[7] + exports[8] + exports[9] + exports[10] + exports[11]   
bysort reportercode year: gen imports_total = imports[1] + imports[2] + imports[3] + imports[4] + imports[5] + imports[6] + imports[7] + imports[8] + imports[9] + imports[10] + imports[11]   
gen impexp_total = exports_total + imports_total

bysort reportercode year: keep if _n==1
drop partnercode imports exports exports_total imports_total 
erase $directory\Daten\untrade_temp_imp.dta
reshape wide impexp_total, i(year) j(reportercode) 

* index to 1993 to get rid of national currencies

foreach i in 208 276 752 757	{
	gen a_impexp_total`i' = .
	
	foreach num of numlist 1/21	{
		replace a_impexp_total`i' = (impexp_total`i'[`num'] / impexp_total`i'[2]) * 100 if _n==[`num']  
		}
		
	drop impexp_total`i'
	rename a_impexp_total`i' impexp_total`i'
	}

* consolidate
gen trade_dkswech = (impexp_total208 + impexp_total752 + impexp_total757) / 3		/* index composed of DK SWE and CH with equal weights */
gen trade_swech = (impexp_total752 + impexp_total757) / 2							/* index composed of SWE and CH with equal weights */

drop impexp_total208 impexp_total752 impexp_total757  
rename impexp_total276 trade_ger  
label variable trade_dkswech "import+export index for DK SWE CH with 10 East Europ. states and China"
label variable trade_swech "import+export index for SWE CH with 10 East Europ. states and China"
label variable trade_ger "import+export index for GER with 10 East Europ. states and China"

* generate %change to prev. year
gen delta_trade_dkswech = .
gen delta_trade_swech = . 
gen delta_trade_ger = .

foreach num of numlist 1/21		{
	replace delta_trade_dkswech = (trade_dkswech[`num']/trade_dkswech[`num' - 1] - 1) * 100 if _n==`num'
	replace delta_trade_swech = (trade_swech[`num']/trade_swech[`num' - 1] - 1) * 100 if _n==`num'
	replace delta_trade_ger = (trade_ger[`num']/trade_ger[`num' - 1] - 1) * 100 if _n==`num'
	}

compress
drop if year==1992
erase $directory\Daten\untrade.dta
saveold $directory\Output\trade_93-12.dta , replace

*** END ***
